﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NTQOnlineExam.Services.Infrastructure;
using NTQOnlineExam.Services.Models;
using NTQOnlineExam.Services.SearchCriteria;

namespace NTQOnlineExam.Services.ReportService.Portfolio
{
    public class GetUserActivePortfolioDbCommand : ICommand<SearchResult<PortfolioDTO>>
    {
        private readonly int _userId;
        private readonly int _pageSize;
        private readonly int _pageIndex;
        private readonly bool _showUnPublishedTerm;
        private readonly SearchCriteria.SearchCriteria _searchCriteria;
        private const string _sql = @"
                 WITH PAGING AS (
                    SELECT p.*, t.IsPublished, t.TermName as TermRealName, row_number() over (order by {0}) as rownumber
                    FROM dbo.Portfolio p INNER JOIN dbo.Term t ON p.TermId = t.TermId
                    WHERE (@ShowUnPublishTerm = 1 OR t.IsPublished = 1) AND p.UserId = @UserId 
                        AND CAST(p.ExpiredDate AS DATE) >= CAST(GetDate() AS DATE)
                        AND (@Keyword IS NULL OR t.TermName LIKE '%' + @Keyword +'%' OR CAST(t.TermId as nvarchar(20)) LIKE '%' + @Keyword +'%' )
                )
        ";

        public GetUserActivePortfolioDbCommand(bool showUnPublishedTerm, SearchCriteria.SearchCriteria searchCriteria, int userId, int pageSize, int pageIndex)
        {
            _pageSize = pageSize;
            _pageIndex = pageIndex;
            _searchCriteria = searchCriteria;
            _showUnPublishedTerm = showUnPublishedTerm;
            _userId = userId;
        }

        public SearchResult<PortfolioDTO> Execute()
        {
            var connectionString = ConfigurationManager.AppSettings["ConnectionString"];
            using (var connection = new SqlConnection(connectionString))
            {
                IList<PortfolioDTO> items = new List<PortfolioDTO>();
                int totalCount = 0;
                var cmd = new SqlCommand(BuildQuery(), connection);
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = _pageIndex;
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = _pageSize;
                cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = _userId;
                cmd.Parameters.Add("@ShowUnPublishTerm", SqlDbType.Bit).Value = _showUnPublishedTerm;
                if (_searchCriteria != null && !string.IsNullOrWhiteSpace(_searchCriteria.Keyword)) cmd.Parameters.Add("@Keyword", SqlDbType.NVarChar).Value = _searchCriteria.Keyword;
                else cmd.Parameters.Add("@Keyword", SqlDbType.NVarChar).Value = DBNull.Value;
                connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                try
                {
                    if (!reader.Read()) return null;
                    totalCount = reader.GetInt32(0);
                    reader.NextResult();

                    while (reader.Read())
                    {
                        items.Add(GetDto(reader));
                    }
                }
                finally
                {
                    reader.Close();
                }
                return new SearchResult<PortfolioDTO>(items, totalCount);
            }
        }

        private PortfolioDTO GetDto(SqlDataReader reader)
        {
            var dto = new PortfolioDTO()
            {
                PortfolioId = (Guid)reader["PortfolioId"],
                OrderId = reader["OrderId"] != DBNull.Value ? (Guid)reader["OrderId"] : Guid.Empty,
                UserId = (int)reader["UserId"],
                TermId = (int)reader["TermId"],
                IsTermPublished = (bool)reader["IsPublished"],
                TermName = reader["TermRealName"] != DBNull.Value ? reader["TermRealName"].ToString() : string.Empty,
                LatestScore = reader["LatestScore"] != DBNull.Value ? (int?)reader["LatestScore"] : null,
                HighestScore = reader["HighestScore"] != DBNull.Value ? (int?)reader["HighestScore"] : null,
                ExpiredDate = reader["ExpiredDate"] != DBNull.Value ? (DateTime)reader["ExpiredDate"] : DateTime.MinValue,
                OrderDate = reader["OrderDate"] != DBNull.Value ? (DateTime)reader["OrderDate"] : DateTime.MinValue,
            };

            return dto;
        }


        private string BuildQuery()
        {
            var sql = string.Format(_sql, BuildOrderClause());
            var query = new StringBuilder();
            query.AppendFormat("{0} {1};{0} {2}", sql, @"SELECT COUNT(1) FROM paging", @" SELECT * FROM paging WHERE (@PageSize=0 or rownumber between @PageSize*@PageIndex+1 and @PageSize*(@PageIndex+1)) ORDER BY rownumber");
            return query.ToString();
        }

      
        private string BuildOrderClause()
        {
            var orderClause = new StringBuilder();
            foreach (SearchSortItem sortItem in _searchCriteria.SortItems)
            {
                if (sortItem.SortExpression == "TermName")
                {
                    orderClause.AppendFormat("t.TermName {0}", sortItem.SortOrder == SortOrder.Ascending ? "ASC" : "DESC");
                    orderClause.Append(',');
                }
                else
                {
                    orderClause.AppendFormat("p.{0} {1}", sortItem.SortExpression, sortItem.SortOrder == SortOrder.Ascending ? "ASC" : "DESC");
                    orderClause.Append(',');
                }

            }

            if (orderClause.Length <= 0)
            {
                orderClause.Append("p.OrderDate desc ");
            }
            else
            {
                orderClause.Remove(orderClause.Length - 1, 1);
            }
            return orderClause.ToString();
        }
    }
}
